package DB;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


public class DBProvider {
	
	private Connection mCon;
	private PreparedStatement mPreStmt;
	private Statement mStmt;
	private ResultSet res;
	private String Puser;
	private String Ppassword;
	private String Purl;
	private String PdbName;
	private String Pdriver;
	
	public DBProvider(Properties p) throws ClassNotFoundException, SQLException, FileNotFoundException, IOException{
		
		String Puser = p.getProperty("DBServer.user");
		String Ppassword = p.getProperty("DBServer.password");;
		String Purl = p.getProperty("DBServer.url");
		String PdbName = p.getProperty("DBServer.dbName");
		String Pdriver = p.getProperty("DBServer.driver");
		
	    // Load the Driver class. 
		Class.forName(Pdriver); 
		
		//Create the connection using the static getConnection method 
		mCon = DriverManager.getConnection (Purl+PdbName, Puser, Ppassword);
	}
	
	private void connect() throws ClassNotFoundException, SQLException {
		
		// Load the Driver class. 
		Class.forName(Pdriver); 
		
		//Create the connection using the static getConnection method 
		mCon = DriverManager.getConnection (Purl+PdbName, Puser, Ppassword); 
	}
	
	public void initDB () throws SQLException, ClassNotFoundException{
		

		if (!mCon.isValid(5)){
			connect();
		}
		
		mStmt = mCon.createStatement();
		
	    //create user table
		mStmt.executeUpdate("CREATE TABLE IF NOT EXISTS user "             +
		                   "(username NVARCHAR(8) NOT NULL, "             + 
		                   "password NVARCHAR(8) NOT NULL, "              + 
                           "first_name NVARCHAR(15) NOT NULL, "           + 
                           "last_name NVARCHAR(15) NOT NULL, "            +
                           "phone_number NVARCHAR(15) NOT NULL, "         +
                           "administrator BOOL NOT NULL, "                +  
                           "PRIMARY KEY (username));");
		
		//create course table
		mStmt.executeUpdate("CREATE TABLE IF NOT EXISTS course "              +
                   		   "(course_title CHAR(50) NOT NULL, "           + 
                   		   "description TEXT NOT NULL, "            + 
                   		   "number_credit_points INTEGER NOT NULL, "         + 
                   		   "course_capacity INTEGER NOT NULL, "              +
                   		   "group_number INTEGER NOT NULL, "                 +
                		   "PRIMARY KEY (course_title)," +
                		   "FULLTEXT(course_title,description))" +
                		   "ENGINE=MyISAM;");	
		
		
		//create user_course table 
		mStmt.executeUpdate("CREATE TABLE IF NOT EXISTS user_course "                        +
        		   		   "(course_title CHAR(50) NOT NULL, "                          + 
        		           "username NVARCHAR(8) NOT NULL, "             		            +
     		   	           "FOREIGN KEY (username) REFERENCES user(username)" +
     		   	           "ON DELETE CASCADE);");	
		
		//create course_sessions table 
		mStmt.executeUpdate("CREATE TABLE IF NOT EXISTS course_sessions "                       +
        		   		   "(course_title CHAR(50) NOT NULL, "      	                   + 
        		           "start_time_slot INTEGER NOT NULL, "             	               +
        		           "end_time_slot INTEGER NOT NULL, "             		               +
     		   	           "PRIMARY KEY(course_title,start_time_slot,end_time_slot));");	
		
		//create xslt_user table 
		mStmt.executeUpdate("CREATE TABLE IF NOT EXISTS xslt_user "                       +
        		   		   "(id INTEGER NOT NULL auto_increment, "             		      +
        		           "username NVARCHAR(15) NOT NULL, "      	                   +
        		           "xslt_file BLOB NOT NULL, "             	               +
     		   	           "PRIMARY KEY(id), "         +
     		   	           "FOREIGN KEY (username) REFERENCES user(username)" +
     		   	           "ON DELETE CASCADE);");	
	}
	
	public ResultSet userDetails(String user) throws SQLException, ClassNotFoundException {
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("SELECT * from user WHERE username = ?");
		mPreStmt.setString(1, user);
		
		return  res = mPreStmt.executeQuery();
		
	}
	
	public ResultSet courseDetails(String title) throws SQLException, ClassNotFoundException {
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("SELECT * from course WHERE course_title = ?");
		mPreStmt.setString(1, title);
		
		return  mPreStmt.executeQuery();
		
	}
	
	public ResultSet getGroupCourses(int group) throws SQLException, ClassNotFoundException {
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("SELECT course_title from course WHERE group_number = ?");
		mPreStmt.setInt(1, group);
		
		return  res = mPreStmt.executeQuery();
		
	}
	
public ResultSet getCoursesNames() throws SQLException, ClassNotFoundException {
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("SELECT course_title from course");
		
		return  res = mPreStmt.executeQuery();
		
	}


	 public void addCourse(  String title,
							 String description,
							 int number_credit_points, 
							 int course_capacity, 
							 int group_number ) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
		connect();
		}
		
		mPreStmt = mCon.prepareStatement("INSERT INTO course (course_title, description, number_credit_points, course_capacity, group_number) " +
		"VALUES(?,?,?,?,?)");
		
		mPreStmt.setString(1, title);
		mPreStmt.setString(2, description);
		mPreStmt.setInt(3, number_credit_points);
		mPreStmt.setInt(4, course_capacity);
		mPreStmt.setInt(5, group_number);
		
		mPreStmt.executeUpdate();
		}
	 
	 
	 
	 public void addSession( String title,
			 				int start_time, 
			 				int end_time) throws SQLException, ClassNotFoundException{

		if (!mCon.isValid(5)){
		connect();
		}
		
		mPreStmt = mCon.prepareStatement("INSERT INTO course_sessions (course_title, start_time_slot, end_time_slot) " +
		"VALUES(?,?,?)");
		
		mPreStmt.setString(1, title);
		mPreStmt.setInt(2, start_time);
		mPreStmt.setInt(3, end_time);
		
		mPreStmt.executeUpdate();
		}
	 
	 
	 public ResultSet getCourseSessions( String title) throws SQLException, ClassNotFoundException{

			if (!mCon.isValid(5)){
				connect();
			}
			
			mPreStmt = mCon.prepareStatement("SELECT * from course_sessions WHERE course_title = ?");
			mPreStmt.setString(1, title);
			
			return  res = mPreStmt.executeQuery();
			
		}
	 
	 	public ResultSet getCoursesUser( String username) throws SQLException, ClassNotFoundException{

			if (!mCon.isValid(5)){
				connect();
			}
			
			mPreStmt = mCon.prepareStatement("SELECT * from user_course u,course c " +
												"WHERE c.course_title = u.course_title AND u.username = ?");

			mPreStmt.setString(1, username);
			
			return  res = mPreStmt.executeQuery();
			
		}
	 	
	 	
	 	public ResultSet getNumberOfRegCourseUsers(String title) throws SQLException, ClassNotFoundException
	 	{

			if (!mCon.isValid(5)){
				connect();
			}
			
			mPreStmt = mCon.prepareStatement("SELECT COUNT(*) FROM user_course WHERE course_title = ?");
			mPreStmt.setString(1, title);
			
			return res = mPreStmt.executeQuery();
			
		}

	
	
	public void registerUser(String user,
							 String password,
							 String firstName, 
							 String lastName, 
							 String phoneNumber, 
							 boolean admin ) throws SQLException, ClassNotFoundException{
	
		if (!mCon.isValid(5)){
			connect();
		}
		
		 mPreStmt = mCon.prepareStatement("INSERT INTO user (username, password, first_name, last_name, phone_number, administrator) " +
			"VALUES(?,?,?,?,?,?)");
		 
		 mPreStmt.setString(1, user);
		 mPreStmt.setString(2, password);
		 mPreStmt.setString(3, firstName);
		 mPreStmt.setString(4, lastName);
		 mPreStmt.setString(5, phoneNumber);
		 mPreStmt.setBoolean(6, admin);
		 
		 mPreStmt.executeUpdate();
	}
	
	
	public void courseRegister(String user,String title ) throws SQLException, ClassNotFoundException{

		if (!mCon.isValid(5)){
		connect();
		}
		mCon.setAutoCommit(false);
		mCon.setTransactionIsolation(mCon.TRANSACTION_SERIALIZABLE);
		
		try
		{
			mPreStmt = mCon.prepareStatement("SELECT * FROM course " +
											 "WHERE ((( SELECT count(*) FROM user_course " +
											 "WHERE course_title = ? ) < course_capacity) " +
											 "AND course_title = ?);");
			
			mPreStmt.setString(1, title);
			mPreStmt.setString(2, title);
			res = mPreStmt.executeQuery();
			
			if(!res.next()){
			 throw (new SQLException()); // FAILED
			}
			
			mPreStmt = mCon.prepareStatement("INSERT INTO user_course (course_title ,username) " +
											 "VALUES(?,?)");
			mPreStmt.setString(1, title);
			mPreStmt.setString(2, user);
			mPreStmt.executeUpdate();
			
			mCon.setAutoCommit(true);
			mCon.setTransactionIsolation(mCon.TRANSACTION_REPEATABLE_READ);
		
		}
		catch(SQLException e)
		{
			mCon.rollback();
			mCon.setAutoCommit(true);
			mCon.setTransactionIsolation(mCon.TRANSACTION_REPEATABLE_READ);
			throw e;
			
		}
		
		
	}
	
	public void updateUserProfile(String user,
			 					  String firstName, 
			 					  String lastName, 
			 					  String phoneNumber) throws SQLException, ClassNotFoundException{
		if (!mCon.isValid(5)){
			connect();
		}
		mPreStmt = mCon.prepareStatement("UPDATE user SET first_name=?, last_name=?, phone_number=? " +
										 "WHERE username=?");
		mPreStmt.setString(1, firstName);
		mPreStmt.setString(2, lastName);
		mPreStmt.setString(3, phoneNumber);
		mPreStmt.setString(4, user);

		mPreStmt.executeUpdate();	
	}
	
	public void updateUserPassword(String user, String password)throws SQLException, ClassNotFoundException{

			if (!mCon.isValid(5)){
				connect();
			}

			mPreStmt = mCon.prepareStatement("UPDATE user SET password=? " +
					 						 "WHERE username=?");
			
			mPreStmt.setString(1, password);
			mPreStmt.setString(2, user);
			
			mPreStmt.executeUpdate();	
	}
	
	public ResultSet getUsersDetails(int start) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("SELECT * FROM user ORDER BY username LIMIT ? , 6");
		mPreStmt.setInt(1, start);
		
		return  res = mPreStmt.executeQuery();
	}
	
	public ResultSet searchCourseByfield(String field,String keyword, int start) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement(" SELECT * FROM course" +
										 " WHERE "+field+" LIKE '%"+keyword+"%'");
		
		
		//mPreStmt.setInt(1, start);
		
		return  res = mPreStmt.executeQuery();
	}
	
	
	public ResultSet fullTextSearch(String keyword, int start) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement(" SELECT * FROM course" +
										 " WHERE MATCH (course_title,description) AGAINST('"+keyword+"') ");
		//mPreStmt.setInt(1, start);
										 
		
		return  res = mPreStmt.executeQuery();
	}
	
	public ResultSet userCouresAndSessions(String user) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("SELECT c.course_title, start_time_slot, end_time_slot " +
										 "FROM user_course u, course_sessions c " +
										 "WHERE c.course_title = u.course_title AND " +
										 "u.username = ? ORDER BY start_time_slot");
		mPreStmt.setString(1, user);
		
		return  res = mPreStmt.executeQuery();
	}
	
	public ResultSet returnNumberXSLTFile(String user) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("SELECT id " +
										 "FROM xslt_user " +
										 "WHERE username = ? ");
		mPreStmt.setString(1, user);
		
		return  res = mPreStmt.executeQuery();
	}
	
	public void deleteUser(String user) throws SQLException, ClassNotFoundException
	{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("DELETE FROM user " +
										 "WHERE username = ? ");
		mPreStmt.setString(1, user);
		
		mPreStmt.executeUpdate();
		
		return ; 
	}
	
	public void deletereg(String user,String title) throws SQLException, ClassNotFoundException
	{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt = mCon.prepareStatement("DELETE FROM user_course " +
										 "WHERE username = ? And course_title = ?");
		mPreStmt.setString(1, user);
		mPreStmt.setString(2, title);
		
		mPreStmt.executeUpdate();
		
		return ; 
	}
	
	
	
	public void deleteCourse(String title) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}
		mCon.setAutoCommit(false);
		try {
					
					mPreStmt = mCon.prepareStatement("DELETE FROM course " +
													 "WHERE course_title = ? ");
					mPreStmt.setString(1, title);
					mPreStmt.executeUpdate();

					mPreStmt = mCon.prepareStatement("DELETE FROM course_sessions " +
					 									"WHERE course_title = ? ");
					mPreStmt.setString(1, title);
					mPreStmt.executeUpdate();
					
					mPreStmt = mCon.prepareStatement("DELETE FROM user_course " +
															"WHERE course_title = ? ");
					mPreStmt.setString(1, title);
					mPreStmt.executeUpdate();
					mCon.commit();
					mCon.setAutoCommit(true);
					
		}
		catch(SQLException e)
		{
			mCon.rollback();
			mCon.setAutoCommit(true);
			throw e;
			
		}
		return ; 
	}
	
	
	public void saveXSLTInDB(String user, ByteArrayOutputStream biteArray) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}

		mPreStmt= mCon.prepareStatement("INSERT INTO xslt_user(username,xslt_file) " +
										"VALUES (?, ?); ");
	
		mPreStmt.setString(1, user);
		mPreStmt.setBlob(2, new ByteArrayInputStream(biteArray.toByteArray()));
		mPreStmt.execute();
	}
	
	public ResultSet getXSlTFile(String user,int id) throws SQLException, ClassNotFoundException{
		
		if (!mCon.isValid(5)){
			connect();
		}
		
		mPreStmt= mCon.prepareStatement("SELECT xslt_file " +
										"FROM xslt_user " +
										"WHERE username = ? AND id = ?");

		mPreStmt.setString(1, user);
		mPreStmt.setInt(2, id);
		
		return res = mPreStmt.executeQuery();
	}

	public void finalize()
	{
		
		if(res!=null){
		     try{res.close();}	catch(Exception e){e.printStackTrace();} }
		if(mPreStmt !=null){
		     try{mPreStmt.close();}	catch(Exception e){e.printStackTrace();} }
		if(mStmt !=null){
		     try{mStmt.close();}	catch(Exception e){e.printStackTrace();} }
		if(mCon !=null){
		     try{mCon.close();}	catch(Exception e){e.printStackTrace();} }
		
	}
	

}
